﻿// Copyright 2009 Mike Geise
// 
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
// 
//     http://www.apache.org/licenses/LICENSE-2.0
// 
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

using System;

namespace Falcon.Core.SqlServer.Repositories
{
	using System.Collections.Generic;
	using System.Configuration;
	using System.Data;
	using System.Data.SqlClient;
	using Falcon.Core.Collections;
	using Falcon.Core.Domain;
	using Falcon.Core.Extensions;
	using Falcon.Core.SqlServer.Repositories.Parameters;
	using Falcon.Core.SqlServer.Repositories.Readers;

	public class SqlRepositoryBase
	{
		/// <summary>
		/// Initializes a new instance of the <see cref="SqlRepositoryBase"/> class.
		/// </summary>
		public SqlRepositoryBase()
		{
			this.ConnectionString = ConfigurationManager.ConnectionStrings["SqlServer"].ConnectionString;
		}

		/// <summary>
		/// Gets or sets the connection string.
		/// </summary>
		/// <value>The connection string.</value>
		public string ConnectionString
		{ 
			get; 
			private set;
		}

		/// <summary>
		/// Returns the connection.
		/// </summary>
		/// <returns></returns>
		public SqlConnection Connection()
		{
			return new SqlConnection(this.ConnectionString);
		}

		/// <summary>
		/// Gets the <see cref="T"/> by specification.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <typeparam name="TSpecification">The type of the K.</typeparam>
		/// <param name="table">The table.</param>
		/// <param name="parameters">The parameters.</param>
		/// <param name="value">The value.</param>
		/// <param name="specification">The specification.</param>
		/// <returns></returns>
		public T GetBy<T, TSpecification>(string table, ISpecificationParameter<TSpecification> parameters, IReader<T> value, TSpecification specification)
			where T : class, IEntity<T>, new()
			where TSpecification : class, ISpecification, new()
		{
			return this.GetBy(table, "dbo.{0}_GetBy", parameters, value, specification);
		}

		/// <summary>
		/// Gets the <see cref="T"/> by specification.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <typeparam name="TSpecification">The type of the K.</typeparam>
		/// <param name="table">The table.</param>
		/// <param name="procedure">The procedure.</param>
		/// <param name="parameters">The parameters.</param>
		/// <param name="value">The value.</param>
		/// <param name="specification">The specification.</param>
		/// <returns></returns>
		public T GetBy<T, TSpecification>(string table, string procedure, ISpecificationParameter<TSpecification> parameters, IReader<T> value, TSpecification specification)
			where T : class, IEntity<T>, new()
			where TSpecification : class, ISpecification, new()
		{
			using (var connection = this.Connection())
			{
				using (var command = new SqlCommand(procedure.Fmt(table), connection))
				{
					command.CommandType = CommandType.StoredProcedure;

					parameters.Populate(command.Parameters, specification);

					connection.Open();

					using (var reader = command.ExecuteReader())
					{
						if (reader != null)
						{
							if (reader.Read())
							{
								var entity = new T();

								value.Populate(reader, entity);

								return entity;
							}

							reader.Close();
						}
					}

					connection.Close();
				}
			}

			return null;
		}

		/// <summary>
		/// Gets the <see cref="T"/> by id.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="table">The table.</param>
		/// <param name="value">The value.</param>
		/// <param name="id">The id.</param>
		/// <returns></returns>
		public T GetById<T>(string table, IReader<T> value, int id)
			where T : class, IEntity<T>, new()
		{
			using (var connection = this.Connection())
			{
				using (var command = new SqlCommand("dbo.{0}_GetByID".Fmt(table), connection))
				{
					command.CommandType = CommandType.StoredProcedure;

					command.Parameters.Add("@ID", SqlDbType.Int).Value = id;

					connection.Open();

					using (var reader = command.ExecuteReader())
					{
						if (reader != null)
						{
							if (reader.Read())
							{
								var entity = new T();

								value.Populate(reader, entity);

								return entity;
							}

							reader.Close();
						}
					}

					connection.Close();
				}
			}

			return null;
		}

		/// <summary>
		/// Gets all the <see cref="T"/>.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="table">The table.</param>
		/// <param name="value">The value.</param>
		/// <returns></returns>
		public IList<T> GetAll<T>(string table, IReader<T> value)
			where T : class, IEntity<T>, new()
		{
			return this.GetAll(table, "dbo.{0}_GetAll", value);
		}

		/// <summary>
		/// Gets all the <see cref="T"/>.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="table">The table.</param>
		/// <param name="procedure">The procedure.</param>
		/// <param name="value">The value.</param>
		/// <returns></returns>
		public IList<T> GetAll<T>(string table, string procedure, IReader<T> value)
			where T : class, IEntity<T>, new()
		{
			var items = new List<T>();

			using (var connection = this.Connection())
			{
				using (var command = new SqlCommand(procedure.Fmt(table), connection))
				{
					command.CommandType = CommandType.StoredProcedure;

					connection.Open();

					using (var reader = command.ExecuteReader())
					{
						if (reader != null)
						{
							while (reader.Read())
							{
								var entity = new T();

								value.Populate(reader, entity);

								items.Add(entity);
							}

							reader.Close();
						}
					}

					connection.Close();
				}
			}

			return items;
		}

		/// <summary>
		/// Gets all the <see cref="T"/> by specification.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <typeparam name="TSpecification">The type of the K.</typeparam>
		/// <param name="table">The table.</param>
		/// <param name="parameters">The parameters.</param>
		/// <param name="value">The value.</param>
		/// <param name="specification">The specification.</param>
		/// <returns></returns>
		public IList<T> GetAll<T, TSpecification>(string table, ISpecificationParameter<TSpecification> parameters, IReader<T> value, TSpecification specification)
			where T : class, IEntity<T>, new()
			where TSpecification : class, ISpecification, new()
		{
			return this.GetAll(table, "dbo.{0}_GetAll", parameters, value, specification);
		}

		/// <summary>
		/// Gets all the <see cref="T"/> by specification.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <typeparam name="TSpecification">The type of the K.</typeparam>
		/// <param name="table">The table.</param>
		/// <param name="procedure">The procedure.</param>
		/// <param name="parameters">The parameters.</param>
		/// <param name="value">The value.</param>
		/// <param name="specification">The specification.</param>
		/// <returns></returns>
		public IList<T> GetAll<T, TSpecification>(string table, string procedure, ISpecificationParameter<TSpecification> parameters, IReader<T> value, TSpecification specification)
			where T : class, IEntity<T>, new()
			where TSpecification : class, ISpecification, new()
		{
			var items = new List<T>();

			using (var connection = this.Connection())
			{
				using (var command = new SqlCommand(procedure.Fmt(table), connection))
				{
					command.CommandType = CommandType.StoredProcedure;

					parameters.Populate(command.Parameters, specification);

					connection.Open();

					using (var reader = command.ExecuteReader())
					{
						if (reader != null)
						{
							while (reader.Read())
							{
								var entity = new T();

								value.Populate(reader, entity);

								items.Add(entity);
							}

							reader.Close();
						}
					}

					connection.Close();
				}
			}

			return items;
		}

		/// <summary>
		/// Gets all the <see cref="T"/> by specification using paging.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <typeparam name="TSpecification">The type of the K.</typeparam>
		/// <param name="table">The table.</param>
		/// <param name="parameters">The parameters.</param>
		/// <param name="value">The value.</param>
		/// <param name="specification">The specification.</param>
		/// <returns></returns>
		public IPagedList<T, TSpecification> GetPaged<T, TSpecification>(string table, ISpecificationParameter<TSpecification> parameters, IReader<T> value, TSpecification specification)
			where T : class, IEntity<T>, new()
			where TSpecification : class, ISpecification, new()
		{
			return GetPaged(table, "dbo.{0}_GetPaged", parameters, value, specification);
		}

		/// <summary>
		/// Gets all the <see cref="T"/> by specification using paging.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <typeparam name="TSpecification">The type of the K.</typeparam>
		/// <param name="table">The table.</param>
		/// <param name="procedure">The procedure.</param>
		/// <param name="parameters">The parameters.</param>
		/// <param name="value">The value.</param>
		/// <param name="specification">The specification.</param>
		/// <returns></returns>
		public IPagedList<T, TSpecification> GetPaged<T, TSpecification>(string table, string procedure, ISpecificationParameter<TSpecification> parameters, IReader<T> value, TSpecification specification)
			where T : class, IEntity<T>, new()
			where TSpecification : class, ISpecification, new()
		{
			// items
			var items = new List<T>();

			// start query
			using (var connection = this.Connection())
			{
				using (var command = new SqlCommand(procedure.Fmt(table), connection))
				{
					command.CommandType = CommandType.StoredProcedure;

					parameters.Populate(command.Parameters, specification);

					command.Parameters.Add("@Start", SqlDbType.Int).Value = specification.Start;
					command.Parameters.Add("@End", SqlDbType.Int).Value = specification.End;

					connection.Open();

					using (var reader = command.ExecuteReader())
					{
						if (reader != null)
						{
							while (reader.Read())
							{
								var entity = new T();

								value.Populate(reader, entity);

								items.Add(entity);
							}

							reader.Close();
						}
					}

					connection.Close();
				}
			}

			// total items
			var total = this.Count(table, parameters, specification);

			// paged list
			return new PagedList<T, TSpecification>(specification, items, total);
		}

		/// <summary>
		/// Gets the total count of <see cref="T"/>.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="table">The table.</param>
		/// <param name="parameters">The parameters.</param>
		/// <param name="specification">The specification.</param>
		/// <returns></returns>
		public int Count<T>(string table, ISpecificationParameter<T> parameters, T specification)
			where T : class, ISpecification, new()
		{
			int count;

			using (var connection = this.Connection())
			{
				using (var command = new SqlCommand("dbo.{0}_Count".Fmt(table), connection))
				{
					command.CommandType = CommandType.StoredProcedure;

					parameters.Populate(command.Parameters, specification);

					connection.Open();

					count = command.ExecuteScalar().ToString().AsInt();

					connection.Close();
				}
			}

			return count;
		}

		/// <summary>
		/// Inserts the specified <see cref="T"/>.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="table">The table.</param>
		/// <param name="parameters">The parameters.</param>
		/// <param name="entity">The entity.</param>
		public void Insert<T>(string table, IParameter<T> parameters, T entity) 
			where T : class, IEntity<T>
		{
			using (var connection = this.Connection())
			{
				using (var command = new SqlCommand("dbo.{0}_Insert".Fmt(table), connection))
				{
					command.CommandType = CommandType.StoredProcedure;

					// identity parameter
					command.Parameters.Add(new SqlParameter
					{
						ParameterName = "@ID",
						Direction = ParameterDirection.Output, 
						SqlDbType = SqlDbType.Int
					});

					// populate parameters
					parameters.Populate(command.Parameters, entity);

					connection.Open();

					// execute insert
					command.ExecuteNonQuery();

					// identity value
					entity.Id = (int)command.Parameters["@ID"].Value;

					connection.Close();
				}
			}
		}

		/// <summary>
		/// Updates the specified <see cref="T"/>.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="table">The table.</param>
		/// <param name="parameters">The parameters.</param>
		/// <param name="entity">The entity.</param>
		public void Update<T>(string table, IParameter<T> parameters, T entity)
			where T : class, IEntity<T>
		{
			using (var connection = this.Connection())
			{
				using (var command = new SqlCommand("dbo.{0}_Update".Fmt(table), connection))
				{
					command.CommandType = CommandType.StoredProcedure;

					if (entity.Id <= 0)
					{
						return;
					}

					command.Parameters.Add("@ID", SqlDbType.Int).Value = entity.Id;

					parameters.Populate(command.Parameters, entity);

					connection.Open();

					command.ExecuteNonQuery();

					connection.Close();
				}
			}
		}

		/// <summary>
		/// Deletes the specified <see cref="T"/>.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="table">The table.</param>
		/// <param name="entity">The entity.</param>
		public void Delete<T>(string table, T entity)
			where T : class, IEntity<T>
		{
			using (var connection = this.Connection())
			{
				using (var command = new SqlCommand("dbo.{0}_Delete".Fmt(table), connection))
				{
					command.CommandType = CommandType.StoredProcedure;

					if (entity.Id <= 0)
					{
						return;
					}

					command.Parameters.Add("@ID", SqlDbType.Int).Value = entity.Id;

					connection.Open();

					command.ExecuteNonQuery();

					connection.Close();
				}
			}
		}
	}
}